package zrz;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import Connection.*;

public class PeopleDAOImpl extends DAOBase implements PeopleDAO {

    private static String ADD_SQL = "INSERT INTO people(p_id,p_name,p_password,p_identity) VALUES(?,?,?,?)";

    private static String GETONE_SQL1 = "SELECT * FROM people WHERE p_name=?";

    private static String GETONE_SQL2 = "SELECT * FROM people WHERE p_id=?";

    private static String GETALL_SQL = "SELECT * FROM people";

    private static String DELETE_SQL="DELETE FROM people WHERE p_id=?";

    private static String UPDATE_SQL="UPDATE people SET p_password=? WHERE p_id=?";

    @Override
    public void addPeople(People people) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(ADD_SQL);
            psmt.setInt(1, people.getP_id());
            psmt.setString(2, people.getP_name());
            psmt.setString(3, people.getP_password());
            psmt.setString(4, people.getP_identity());
            psmt.executeUpdate();
            psmt.close();
            System.out.println("添加成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public People getPeople(String name) {
        People people = new People();
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(GETONE_SQL1);
            psmt.setString(1, name);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                people=new People();
                people.setP_id(rs.getInt("p_id"));
                people.setP_name(rs.getString("p_name"));
                people.setP_password(rs.getString("p_password"));
                people.setP_identity(rs.getString("p_identity"));
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return people;
    }

    @Override
    public People getPeopleById(int id) {
        People people = new People();
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(GETONE_SQL2);
            psmt.setInt(1, id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                people=new People();
                people.setP_id(rs.getInt("p_id"));
                people.setP_name(rs.getString("p_name"));
                people.setP_password(rs.getString("p_password"));
                people.setP_identity(rs.getString("p_identity"));
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return people;
    }

    @Override
    public List<People> getAllPeople() {
        List<People> peoples = Collections.unmodifiableList(new ArrayList<>());
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(GETALL_SQL);
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                People people = new People();
                people.setP_id(rs.getInt("p_id"));
                people.setP_name(rs.getString("p_name"));
                people.setP_password(rs.getString("p_password"));
                people.setP_identity(rs.getString("p_identity"));
                peoples.add(people);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return peoples;
    }

    @Override
    public void updatePeople(int id,String password) {
        Connection con = null;
        try {
            con = getConnection();
            People people1 = getPeopleById(id);
            if (people1.getP_name() == null){
                System.out.println("当前系统没有该用户");
                return;
            }
            PreparedStatement psmt = con.prepareStatement(UPDATE_SQL);
            psmt.setString(1, password);
            psmt.setInt(2, id);
            psmt.executeUpdate();
            psmt.close();
            System.out.println("修改完成");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void delPeople(int id) {
        Connection con = null;
        try {
            con = getConnection();
            People people1 = getPeopleById(id);
            if (people1.getP_name() == null){
                System.out.println("系统中没有该用户");
                return;
            }
            PreparedStatement psmt = con.prepareStatement(DELETE_SQL);
            psmt.setInt(1, id);
            psmt.executeUpdate();
            psmt.close();
            System.out.println("删除成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
